﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Reviewer.Data.Bases;
using MySql.Data.MySqlClient;
using Reviewer.Utilities.Data;
using Reviewer.Entities;
using System.Globalization;
using System.Collections.ObjectModel;
using System.Data;

namespace Reviewer.Data
{
    [CLSCompliant(false)]
    public class GameScreenshotRepository : RepositoryCrudAlternativeBase<GameScreenshot, Int32, Guid>
    {
        #region fields (private)
        private const string TABLE_NAME = "V2_Screenshots";
        private static readonly CrudColumnInfo columnId = new CrudColumnInfo(0, "ID", MySqlDbType.Int32, true, false, false, true);
        private static readonly CrudColumnInfo columnGameId = new CrudColumnInfo(1, "SpilId", MySqlDbType.VarChar, 36, false, true, false);
        private static readonly CrudColumnInfo columnFormat = new CrudColumnInfo(1, "Format", MySqlDbType.VarChar, 10, false, false, true);
        private static readonly CrudColumnInfo columnFilename = new CrudColumnInfo(1, "Filnavn", MySqlDbType.VarChar, 255, false, false, true);
        #endregion

        #region methods (public)
        /// <summary>
        /// Deletes the by filename.
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        public void DeleteByFileName(string fileName)
        {
            using (MySqlCommand command = new MySqlCommand(
                String.Format(CultureInfo.InvariantCulture,
                "DELETE FROM {0} WHERE {1} = @{1}"
                , TABLE_NAME
                , columnFilename.Name)))
            {
                columnFilename.AddParameter(command, fileName);
                ExecuteNonQuery(command);
            }
        }

        public ICollection<GameScreenshotSearchResult> Search(int topCount)
        {
            StringBuilder commandText = new StringBuilder();
            commandText.Append("SELECT ");
            foreach (CrudColumnInfo column in this.Columns)
            {
                commandText.AppendFormat("SC.{0}, ", column.Name);
            }

            commandText.Append("G.Titel AS GameTitle, G.Format AS GameFormat, F.Navn AS GameFormatName, G.Genre as GameGenre, GR.Navn AS GameGenreName");
            commandText.AppendFormat(" FROM {0} SC", GameScreenshotRepository.TABLE_NAME);
            commandText.Append(" LEFT OUTER JOIN V2_Spil G ON (SC.SpilId = G.SpilId)");
            commandText.Append(" LEFT OUTER JOIN V2_GloFormat F on (G.Format = F.Id)");
            commandText.Append(" LEFT OUTER JOIN V2_GloGenres GR on (G.Genre = GR.Id)");
            commandText.Append(" GROUP BY SC.SpilID");
            commandText.Append(" ORDER BY SC.Id DESC");

            if (topCount <= 0)
                topCount = 10;

            commandText.AppendFormat(" LIMIT {0}", topCount);

            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = commandText.ToString();

                Collection<GameScreenshotSearchResult> list = new Collection<GameScreenshotSearchResult>();
                using (IDataReader reader = ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        GameScreenshotSearchResult item = new GameScreenshotSearchResult();
                        item.Id = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnId.Name), 0);
                        item.GameId = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnGameId.Name), Guid.Empty);
                        int formatId = 0;
                        if (int.TryParse(DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnFormat.Name), String.Empty), out formatId))
                        {
                            item.Format = formatId;
                        }
                        else
                        {
                            item.Format = 0;
                        }

                        item.FileName = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnFilename.Name), String.Empty);

                        item.GameTitle = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameTitle"), String.Empty);
                        item.GameFormat = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameFormat"), 0);
                        item.GameFormatName = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameFormatName"), String.Empty);
                        item.GameGenre = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameGenre"), 0);
                        item.GameGenreName = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameGenreName"), String.Empty);
                        list.Add(item);
                    }
                }
                return list;
            }
        }

        public ICollection<GameScreenshotSearchResult> Search(string gameTitle, int? gameFormat, int? gameGenre, int? maxResults)
        {
            StringBuilder commandText = new StringBuilder();
            commandText.Append("SELECT ");
            foreach (CrudColumnInfo column in this.Columns)
            {
                commandText.AppendFormat("SC.{0}, ", column.Name);
            }

            commandText.Append("G.Titel AS GameTitle, G.Format AS GameFormat, F.Navn AS GameFormatName, G.Genre as GameGenre, GR.Navn AS GameGenreName, COUNT(SC.Id) AS ScreenshotCount");
            commandText.AppendFormat(" FROM {0} SC", GameScreenshotRepository.TABLE_NAME);
            commandText.Append(" LEFT OUTER JOIN (SELECT Gi.Titel, Gi.SpilId, Gi.Format, Gi.Genre FROM V2_Spil Gi GROUP BY Gi.SpilId, Gi.Titel) G ON (SC.SpilId = G.SpilId)");
            commandText.Append(" LEFT OUTER JOIN V2_GloFormat F on (G.Format = F.Id)");
            commandText.Append(" LEFT OUTER JOIN V2_GloGenres GR on (G.Genre = GR.Id)");
            commandText.Append(" WHERE 1 = 1");

            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = commandText.ToString();

                if (!String.IsNullOrEmpty(gameTitle))
                {
                    command.CommandText += " AND G.Titel LIKE @GameTitle";
                    command.Parameters.AddWithValue("@GameTitle", gameTitle);
                }

                if (gameFormat.HasValue && gameFormat.Value > 0)
                {
                    command.CommandText += " AND G.Format = " + gameFormat.Value.ToString(CultureInfo.InvariantCulture);
                }

                if (gameGenre.HasValue && gameGenre.Value > 0)
                {
                    command.CommandText += " AND G.Genre = " + gameGenre.Value.ToString(CultureInfo.InvariantCulture);
                }

                command.CommandText += " GROUP BY SC.SpilID";
                command.CommandText += " ORDER BY SC.Id DESC";
                
                if (!maxResults.HasValue)
                {
                    maxResults = 1000;
                }

                if (maxResults.Value > 0)
                {
                    command.CommandText += " LIMIT " + maxResults.Value.ToString(CultureInfo.InvariantCulture);
                }

                Collection<GameScreenshotSearchResult> list = new Collection<GameScreenshotSearchResult>();
                using (IDataReader reader = ExecuteReader(command))
                {
                    while (reader.Read())
                    {
                        GameScreenshotSearchResult item = new GameScreenshotSearchResult();
                        item.Id = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnId.Name), 0);
                        item.GameId = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnGameId.Name), Guid.Empty);
                        int formatId = 0;
                        if (int.TryParse(DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnFormat.Name), String.Empty), out formatId))
                        {
                            item.Format = formatId;
                        }
                        else
                        {
                            item.Format = 0;
                        }

                        item.FileName = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnFilename.Name), String.Empty);

                        item.GameTitle = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameTitle"), String.Empty);
                        item.GameFormat = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameFormat"), 0);
                        item.GameFormatName = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameFormatName"), String.Empty);
                        item.GameGenre = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameGenre"), 0);
                        item.GameGenreName = DataReaderUtility.GetValue(reader, reader.GetOrdinal("GameGenreName"), String.Empty);

                        if (reader.GetOrdinal("ScreenshotCount") >= 0)
                        {
                            item.ScreenshotCount = DataReaderUtility.GetValue(reader, reader.GetOrdinal("ScreenshotCount"), 0);
                        }

                        list.Add(item);
                    }
                }
                return list;
            }
        }

        public ICollection<GameScreenshot> FindByNewsRelation(int newsId)
        {
            using (MySqlCommand command = new MySqlCommand())
            {
                command.CommandText = @"SELECT S.* FROM V2_Nyhed N JOIN V2_NewsRelations NR ON (N.NewsID = NR.NewsID) JOIN V2_Screenshots S ON (NR.SpilId = S.SpilID) WHERE N.Id = @NewsId";
                command.Parameters.AddWithValue("@NewsId", newsId);

                Collection<GameScreenshot> items = this.ExecuteReaderAndBuildList(command);
                List<GameScreenshot> items2 = new List<GameScreenshot>();
                foreach (GameScreenshot item in items)
                {
                    if (!items2.Exists(t => t.FileName.Equals(item.FileName)))
                    {
                        items2.Add(item);
                    }
                }

                return items2;
            }
        }
        #endregion

        #region methods (protected)
        internal override CrudColumnInfo[] Columns
        {
            get
            {
                return new CrudColumnInfo[]{
                    columnId
                    , columnGameId
                    , columnFormat
                    , columnFilename
                };
            }
        }

        protected override string TableName
        {
            get { return GameScreenshotRepository.TABLE_NAME; }
        }

        internal override object ColumnToValue(CrudColumnInfo info, GameScreenshot item)
        {
            if (info.Equals(GameScreenshotRepository.columnId)) return item.Id;
            else if (info.Equals(GameScreenshotRepository.columnGameId)) return item.GameId;
            else if (info.Equals(GameScreenshotRepository.columnFormat)) return item.Format.ToString(CultureInfo.InvariantCulture);
            else if (info.Equals(GameScreenshotRepository.columnFilename)) return item.FileName;
            return null;
        }

        protected override GameScreenshot BuildItemFromReader(System.Data.IDataReader reader)
        {
            GameScreenshot item = new GameScreenshot();
            if (reader != null)
            {
                item.Id = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnId.Name), 0);
                item.GameId = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnGameId.Name), Guid.Empty);
                int formatId = 0;
                if (int.TryParse(DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnFormat.Name), String.Empty), out formatId))
                {
                    item.Format = formatId;
                }
                else
                {
                    item.Format = 0;
                }

                item.FileName = DataReaderUtility.GetValue(reader, reader.GetOrdinal(GameScreenshotRepository.columnFilename.Name), String.Empty);
            }
            return item;
        }
        #endregion
    }
}
